﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;

/**
 * 数据库基本操作 
 * 
 */
namespace GodSystemCommon
{
   
    public class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["csharp_god"].ConnectionString;

        private static MySqlConnection conn = null;
        private static MySqlTransaction transaction = null;
        /// <summary>
        /// 开启事务操作
        /// </summary>
        public static void StartTran()
        {
            if(conn == null)
            {
                conn = new MySqlConnection(connStr);
            }
            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();
            }
            catch(Exception ex)
            {
                TransactionRollback();
                throw;
            }
        }


        /// <summary>
        /// 事务操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryForTran(string sql, params MySqlParameter[] pms)
        {
            try
            {
                if(conn == null)
                {
                    StartTran();
                }
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = transaction;
         
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
               return cmd.ExecuteNonQuery();
            }catch(Exception)
            {
                TransactionRollback();
                throw;
            }
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public static void TransactionRollback()
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public static void TransactionCommit()
        {
            if (transaction != null)
            {
                transaction.Commit();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 招待普通的 增，删，改，并返回影响的记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] pms)
        {
            using(MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.Clear();
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行 ExecuteScalar 方法，返回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params MySqlParameter[] pms)
        {
            using(MySqlConnection conn = new MySqlConnection(connStr))
            {
                using(MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.Clear();
                    if(pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 返回 DataTable 数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params MySqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                using(MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn))
                {
                    adapter.SelectCommand.Parameters.Clear();
                    if(pms != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        /// <summary>
        /// 返回DataSet数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql, params MySqlParameter[] pms)
        {
            DataSet ds = new DataSet();
            using(MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
                {
                    da.SelectCommand.Parameters.Clear();
                    if(pms != null)
                    {
                        da.SelectCommand.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    da.Fill(ds);
                }
            }
            return ds;
        }

        /// <summary>
        /// 返回多行多行数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] pms)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Clear();
            if (pms != null)
            {
                cmd.Parameters.AddRange(pms);
            }
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}
